use master
go

create database ATM
on
(
   name='ATM',
   filename='F:\ATM.mdf',
   size=5MB,
   maxsize=100MB,
   filegrowth=15%
)
log on
(
   name='ATM_log',
   filename='F:\ATM_log.ldf',
   size=5MB,
   maxsize=100MB,
   filegrowth=15%
)
go

use ATM
go

create table userInfo
(
   customerID int primary key identity(1,1),
   customerName nvarchar(10) not null,
   PID nvarchar(18) not null unique check(len(PID) in (15,18)),
   telephone nvarchar(15) not null check((telephone like '____-________') or len(telephone)=13),
   addRess nvarchar(40) 
)

create table cardInfo
(
   cardID varchar(20) primary key check(cardID like '1010 3576 ____ ___')
   default('1010 3576 '+right('0000'+cast(floor(rand()*10000) as varchar),4)+' '+right('000'+cast(floor(rand()*1000) as varchar),3)),
   curType nvarchar(20) not null default'RMB',
   savingType nvarchar(10) check(savingType='活期' or savingType='两便' or savingType='定期'),
   openDate datetime not null default(getdate()),
   balance money not null check(len(balance)>=1),
   pass int not null check(len(pass)=6) default'888888',
   IsReportLoss nvarchar(2) check(IsReportLoss='是' or IsReportLoss='否') default'否',
   customerID int not null references userInfo(customerID)
)

create table transInfo
(
   transInfo int primary key identity(1,1),
   transDate datetime not null default(getdate()),
   cardID int not null references cardInfo(cardID),
   transType nvarchar(2) not null check(transType='存入' or transType='支取'),
   transMoney money not null check(len(transMoney)>0),
   remark nvarchar(100)
)
go

--孙悟空开户，身份证：123456789012345，电话：0716-78989783，地址：北京海淀 
--   开户金额：1000 活期   卡号：1010 3576 1234 567

--沙和尚开户，身份证：421345678912345678，电话：0478-44223333，
--   开户金额： 1  定期 卡号：1010 3576 1212 117

--唐僧开户，身份证：321245678912345678，电话：0478-44443333，
--   开户金额： 1  定期 卡号：1010 3576 1212 113
insert into userInfo(customerName,PID,telephone,address)values
('孙悟空',123456789012345,'0716-78989783','北京海淀'),
('沙和尚',421345678912345678,'0716-78989783',''),
('唐僧',321245678912345678,'0478-44443333','')

insert into cardInfo(cardID,savingType,balance,customerID)values
('1010 3576 1234 567','活期',1000,1),
('1010 3576 1212 117','定期',1,2),
('1010 3576 1212 113','定期',1,3)

--1.将用户“孙悟空”开卡时的初始密码更改为“611234”
update cardInfo set pass='611234' where customerID=1

--2.用两条SQL语句实现孙悟空要取钱(取200)的操作，先向交易信息表插入一条取钱的交易记录，然后在孙悟空账上的余额减200
--注意：先要将用户孙悟空的用户编号找到，再根据用户编号找到卡号，再根据银行卡号来插入交易记录和修改账上余额

select * from transInfo 
select cardID from cardInfo where customerID=(select customerID from userInfo where customerName='孙悟空')

insert into transInfo(cardID,transType,transMoney) values ('1010 3576 1234 567','支取',200)
update cardInfo set balance=balance-200 where cardID='1010 3576 1234 567'

--3.用同上题一样的方法实现沙和尚存钱的操作(存300)
insert into transInfo(cardID,transType,transMoney) values ('1010 3576 1212 117','存入',300)
update cardInfo set balance=balance+300 where cardID='1010 3576 1212 117'

--4.唐僧的卡丢了，需要挂失，将唐僧的银行卡的是否挂失字段的值改为“是”
update cardInfo set IsReportLoss='是' where customerID=3

--5.查询出最近10天开户的银行卡的信息
select * from cardInfo where openDate>=dateadd(dd,-10,getdate())

--6.查询交易金额最大的银行卡信息，子查询实现
select * from cardInfo where cardID =(select cardID from transInfo where transMoney=(select MAX(transMoney)from transInfo))

--7.再交易信息表中，将总的交易金额，支取的交易金额，存入的交易金额查询出来并输出显示
--显示效果：
--总交易金额：1400.00
--支取交易金额：200.00
--存入交易金额：1200.00
select '总交易金额:',SUM(transMoney) from transInfo
union
select '支取交易金额:',transMoney from transInfo where transType ='支取'
union
select '存入交易金额:',transMoney from transInfo where transType ='存入'

--8.催款提醒：假如由于某种业务的需求，每个月末，如果发现卡上账上余额少于200元的，将致电催款，请将卡上余额少于200元的用户的卡号和用户ID查询出来
select cardID,customerID from cardInfo where balance < 200